﻿using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Model;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Text.Json.Nodes;
using System.Threading.Tasks;

namespace DAL
{
    public class WhInventoryDal
    {
        private readonly AppDbContext _dbContext;
        private string URL;
        public WhInventoryDal(AppDbContext dbContext)
        {
            dynamic jsonObject = JsonConvert.DeserializeObject(File.ReadAllText("appsettings.json"));
            _dbContext = dbContext;
            URL = jsonObject.ConnectionStrings.SqlServer;
        }



        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="data">实体</param>
        /// <returns>结果</returns>
        public async Task<int> CreateAsync(WhInventory data)
        {
            // 新增实体到数据库集
            _dbContext.WhInventory.Add(data);
            // 保存更改
            return await _dbContext.SaveChangesAsync();
        }

        /// <summary>
        /// 根据ID查询
        /// </summary>
        /// <param name="Id">ID</param>
        /// <returns>用户列表</returns>
        public async Task<WhInventory> GetByIdAsync(int Id)
        {
            // 根据ID查询
            return await _dbContext.WhInventory.FirstOrDefaultAsync(g => g.InventoryId == Id);
        }

        /// <summary>
        /// 查询所有
        /// </summary>
        /// <returns>用户列表</returns>
        public async Task<List<WhInventory>> GetAllAsync()
        {
            // 查询所有并转换为列表
            return await _dbContext.WhInventory.ToListAsync();
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="date">包含更新信息</param>
        public async Task<int> UpdateAsync(WhInventory date)
        {
            // 标记实体为修改状态
            _dbContext.WhInventory.Update(date);
            // 保存更改
            return await _dbContext.SaveChangesAsync();
        }

        /// <summary>
        /// 根据ID删除
        /// </summary>
        /// <param name="Id">ID</param>
        public async Task<int> DeleteAsync(int Id)
        {
            // 先查询实体
            var data = await GetByIdAsync(Id);
            if (data != null)
            {
                // 移除实体
                _dbContext.WhInventory.Remove(data);
                // 保存更改
                return await _dbContext.SaveChangesAsync();
            }
            return 0;
        }

        /// <summary>
        /// 查询盘点单聚合数据（含货品名称、盘盈盘亏统计等）
        /// </summary>
        public async Task<List<InventoryAggregateDTO>> GetInventoryAggregateListAsync()
        {
            var sql = @"
SELECT 
    wst.inventory_order_id,
    wst.inventory_order_no,
    wst.order_status,
    wst.create_time,
    wst.inventory_type,
    wst.inventory_start_time,
    wst.inventory_end_time,
    wst.warehouse_id,
    bw.warehouse_name,
    COALESCE(STRING_AGG(bp.goods_name, ',') , '') AS goods_names,
    COALESCE(SUM(wstd.inventory_quantity), 0) AS inventory_quantity,
    COALESCE(SUM(CASE WHEN wstd.profit_loss_quantity > 0 THEN wstd.profit_loss_quantity ELSE 0 END), 0) AS total_profit_quantity,
    COALESCE(SUM(CASE WHEN wstd.profit_loss_quantity < 0 THEN ABS(wstd.profit_loss_quantity) ELSE 0 END), 0) AS total_loss_quantity,
    COALESCE(SUM(CASE WHEN wstd.profit_loss_amount > 0 THEN wstd.profit_loss_amount ELSE 0 END), 0) AS total_profit_amount,
    COALESCE(SUM(CASE WHEN wstd.profit_loss_amount < 0 THEN ABS(wstd.profit_loss_amount) ELSE 0 END), 0) AS total_loss_amount,
    COALESCE(u_create.users_name, '') AS create_by,
    COALESCE(so_create.org_name, '') AS create_org_name,
    COALESCE(u_create.users_name, '') AS auditor_by,
    -- 引用表中实际字段名（inbound/outbound）
    wst.inbound,
    wst.outbound
FROM wh_stock_take wst
INNER JOIN bi_warehouse bw ON wst.warehouse_id = bw.warehouse_id
INNER JOIN wh_stock_take_detail wstd ON wst.inventory_order_id = wstd.inventory_order_id
INNER JOIN bi_product bp ON wstd.goods_code = bp.goods_code
JOIN sys_users u_create ON wst.create_by = u_create.users_id
JOIN sys_organization so_create ON u_create.org_id = so_create.org_id
LEFT JOIN sys_users u_audit ON wst.auditor_by = u_audit.users_id
GROUP BY 
    wst.inventory_order_id,
    wst.inventory_order_no,
    wst.order_status,
    wst.create_time,
    wst.inventory_type,
    wst.inventory_start_time,
    wst.inventory_end_time,
    wst.warehouse_id,
    bw.warehouse_name,
    u_create.users_name,
    so_create.org_name,
    u_audit.users_name,
    -- 按实际字段名分组
    wst.inbound,
    wst.outbound;
";

            using (var connection = new SqlConnection(URL))
            {
                await connection.OpenAsync();
                return (await connection.QueryAsync<InventoryAggregateDTO>(sql)).AsList();
            }
        }
        /// <summary>
        /// 五表联查：按仓库+库位查询商品（含货品类型）
        /// </summary>
        public async Task<List<WarehouseLocationGoodsDTO>> GetGoodsByWarehouseAndLocationAsync(long warehouseId, long storageLocationId)
        {
            var query = _dbContext.WhInventory
                // 1. 关联库存表（WhInventory）和货品表（BiProduct）
                .Join(_dbContext.BiProduct,
                    inv => inv.GoodsId,
                    prod => prod.GoodsId,
                    (inv, prod) => new { inv, prod })
                // 2. 关联货品表（BiProduct）和货品类型表（BiProductCategory）
                .Join(_dbContext.goodsTypes,
                    ip => ip.prod.GoodsTypeId,  // 货品表的类型ID
                    cat => cat.GoodsTypeId,     // 类型表的类型ID
                    (ip, cat) => new { ip, cat })
                // 3. 关联库位表（WhLocation）
                .Join(_dbContext.WhLocation,
                    ipc => ipc.ip.inv.StorageLocationId,
                    loc => loc.StorageLocationId,
                    (ipc, loc) => new { ipc, loc })
                // 4. 关联仓库表（BiWarehouse）
                .Join(_dbContext.BiWarehouse,
                    iplc => iplc.loc.WarehouseId,
                    wh => wh.WarehouseId,
                    (iplc, wh) => new WarehouseLocationGoodsDTO
                    {
                        // 商品信息（含类型）
                        GoodsId = iplc.ipc.ip.prod.GoodsId,
                        GoodsCode = iplc.ipc.ip.prod.GoodsCode,
                        GoodsName = iplc.ipc.ip.prod.GoodsName,
                        Specification = iplc.ipc.ip.prod.Specification,
                        GoodsType = iplc.ipc.cat.GoodsTypeName,  // 从类型表获取货品类型名称
                                                                 // 库存信息
                        AvailableQty = iplc.ipc.ip.inv.AvailableInventoryQty, // 这里字段名不匹配
                        UnitPrice = iplc.ipc.ip.inv.UnitPrice,
                        // 库位信息
                        StorageLocationId = iplc.loc.StorageLocationId,
                        StorageLocationName = iplc.loc.StorageLocationName,
                        // 仓库信息
                        WarehouseId = wh.WarehouseId,
                        WarehouseName = wh.WarehouseName
                    })
                // 筛选条件：指定仓库和库位
                .Where(dto => dto.WarehouseId == warehouseId && dto.StorageLocationId == storageLocationId);

            return await query.ToListAsync();
        }

        /// <summary>
        /// 根据仓库ID、货品ID、库位ID查询账面库存 - 使用原始SQL
        /// </summary>
        public async Task<decimal> GetBookInventoryAsync(long warehouseId, long goodsId, long storageLocationId)
        {
            // 数据库连接字符串（建议从配置文件读取，而非硬编码）
            var connectionString = "你的数据库连接字符串URL";

            try
            {
                // 参数化SQL查询
                var sql = @"
                    SELECT 
                        wi.available_inventory_qty  
                    FROM wh_inventory wi 
                    INNER JOIN bi_warehouse bw ON bw.warehouse_id = wi.warehouse_id 
                    INNER JOIN wh_location wl ON wl.storage_location_id = wi.storage_location_id
                    INNER JOIN bi_product bp ON wi.goods_id = bp.goods_id
                    WHERE 
                        bw.warehouse_id = @WarehouseId 
                        AND wl.storage_location_id = @StorageLocationId 
                        AND wi.goods_id = @GoodsId;";

                // 使用using自动管理连接生命周期（创建->打开->使用->释放）
                using (var connection = new SqlConnection(URL))
                {
                    // 打开连接（Dapper会自动打开，但显式打开更清晰）
                    await connection.OpenAsync();

                    // 执行查询
                    var result = await connection.QueryFirstOrDefaultAsync<decimal>(
                        sql,
                        new
                        {
                            WarehouseId = warehouseId,
                            StorageLocationId = storageLocationId,
                            GoodsId = goodsId
                        }
                    );

                    // 调试信息
                    Console.WriteLine($"查询账面库存成功：warehouseId={warehouseId}, goodsId={goodsId}, storageLocationId={storageLocationId}, 结果={result}");

                    return result;
                }
            }
            catch (Exception ex)
            {
                //Console.WriteLine($"查询账面库存异常：{ex.Message}");
                // 异常时返回0
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 根据复合主键查询库存记录
        /// </summary>
        public async Task<WhInventory> GetByCompositeKeyAsync(long warehouseId, long storageLocationId, long goodsId)
        {
            Console.WriteLine($"查询库存条件：warehouseId={warehouseId}, storageLocationId={storageLocationId}, goodsId={goodsId}");

            // 1. 定义 SQL（与原逻辑一致，表名 wh_inventory 无横杠，无需包裹）
            string sql = @"
                            SELECT * 
                            FROM wh_inventory
                            WHERE warehouse_id = @WarehouseId 
                              AND storage_location_id = @StorageLocationId 
                              AND goods_id = @GoodsId";

            // 2. 准备参数（Dapper 支持匿名对象或 DynamicParameters，这里用匿名对象更简洁）
            var parameters = new
            {
                WarehouseId = warehouseId,
                StorageLocationId = storageLocationId,
                GoodsId = goodsId
            };

            // 3. 从 EF Core 的 DbContext 中获取数据库连接（或直接使用配置的连接字符串）
            using (IDbConnection conn = new SqlConnection(_dbContext.Database.GetConnectionString()))
            {
                try
                {
                    var result = await conn.QueryFirstOrDefaultAsync<WhInventory>(sql, parameters);

                    Console.WriteLine($"查询库存结果：{(result == null ? "无记录" : "找到记录")}");
                    return result;
                }
                catch (Exception ex)
                {
                    // 可选：添加异常日志（建议用日志框架，如 Serilog/NLog）
                    Console.WriteLine($"Dapper 查询库存异常：{ex.Message}，堆栈：{ex.StackTrace}");
                    throw; // 抛出异常，让上层业务处理
                }
            }
        }
    }
}
